package com.intelligentmeterreadingsys.waterlist.copywater.db;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.intelligentmeterreadingsys.BaseDao.BaseDao;
import com.intelligentmeterreadingsys.povo.NoticeVo;
import com.intelligentmeterreadingsys.povo.ResourcesPoVo;
import com.intelligentmeterreadingsys.povo.UserWaterVO;
import com.intelligentmeterreadingsys.povo.WaterInfoVO;
import com.intelligentmeterreadingsys.povo.WaterlistPoVo;
import com.intelligentmeterreadingsys.util.DateUtil;

import dbhelper.DbTableName;

/**
 * 抄表与数据库交换数据类
 * 
 * @author king
 * 
 */
public class CopyWaterDao extends BaseDao {

    private SQLiteDatabase db;

    public CopyWaterDao(Context context) {
	super(context);
	this.db = super.getDb();
    }

    /**
     * 通过RFID卡号查询用户及水表信息
     * 
     * @param RFID
     * @return
     */
    public List<UserWaterVO> queryUserWaterInfo(String RFID, String adds, String waterid) {
	List<UserWaterVO> list = new ArrayList<UserWaterVO>();
	UserWaterVO water = null;
	String sql = "select w.[_id] as waterid,w.[isupload],w.[thisdate],w.[desc],w.[thismonthwater], w.[waternumber],w.[usernumber],w.[wateradds], w.[lastmonthwater],w.[waterlistid]"
		+ " ,w.[waterproperties],u.[phone],w.[metercaliber],u.[username],w.[alarmlimit],w.[lowalarmlimit] ,w.[groupingname],w.[groupingsort],w.[groupwatersort] from waterinfo w,userinfo u where "
		+ "w.[usernumber]=u.[usernumber] " + " and w.[ismultiplecopy]='0' and u.[ismultiplecopy]='0'";
	if (RFID != null) {
	    sql = sql + " and w.[RFIDnumber]='" + RFID + "'";
	}
	if (adds != null) {
	    sql = sql + " and w.[wateradds] like '%" + adds + "%'";
	}
	if (waterid != null) {
	    sql = sql + " and w.[_id]=" + waterid;
	}
	System.out.println("读卡SQL" + sql);

	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    water = new UserWaterVO();
	    water.setIsupload(cursor.getString(cursor.getColumnIndex("isupload")));
	    water.setAdds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setMetercaliber(cursor.getString(cursor.getColumnIndex("metercaliber")));
	    water.setName(cursor.getString(cursor.getColumnIndex("username")));
	    water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setAlarmlimit(cursor.getDouble(cursor.getColumnIndex("alarmlimit")));
	    water.setLowalarmlimit(cursor.getDouble(cursor.getColumnIndex("lowalarmlimit")));
	    water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    String thismonthwater = cursor.getString(cursor.getColumnIndex("thismonthwater"));
	    water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    water.setWaterlistId(cursor.getString(cursor.getColumnIndex("waterlistid")));
	    water.setGroupwatersort(cursor.getInt(cursor.getColumnIndex("groupwatersort")));
	    water.setGroupingname(cursor.getString(cursor.getColumnIndex("groupingname")));
	    if (thismonthwater != null && !thismonthwater.equals("")) {
		water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    } else {
		water.setThismonthwater(0.0);
	    }
	    water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
	    water.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
	    water.setGroupingname(cursor.getString(cursor.getColumnIndex("groupingname")));

	    water.setGroupingsort(cursor.getInt(cursor.getColumnIndex("groupingsort")));

	    list.add(water);
	    // String sqluseWater =
	    // "select * from usewaterinfo where waternumber='" +
	    // water.getWaternumber() + "' and ismultiplecopy='0'";
	    // Cursor c = db.rawQuery(sqluseWater, null);
	    // List<Double> lists = new ArrayList<Double>();
	    //
	    // while (c.moveToNext()) {
	    // lists.add(c.getDouble(c.getColumnIndex("watercount")));
	    //
	    // }
	    // if (lists.size() == 1) {
	    // water.setAlarmlimit(lists.get(0) * 1.2);
	    // water.setLowalarmlimit(lists.get(0) * 0.8);
	    // } else if (lists.size() == 0) {
	    // water.setAlarmlimit(0.0);
	    // water.setLowalarmlimit(0.0);
	    // }
	    //
	    // if (c != null) {
	    // c.close();
	    // }
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;

    }

    /**
     * 修改本月的用水刻度
     * 
     * @param RFID
     * @param thismonthwater
     * @return
     */
    public boolean saveWater(String waterid, Double thismonthwater, String desc, String isNoRfid) {
	boolean b = false;
	if (waterid == null | waterid.equals("")) {
	    return false;
	}
	ContentValues values = new ContentValues();
	if (thismonthwater != null) {

	    values.put("thismonthwater", thismonthwater);
	}
	if (desc != null) {
	    values.put("desc", desc);
	}
	values.put("thisdate", DateUtil.getBeforeDay(0));
	values.put("iscopystatus", "已抄");
	values.put("isnorfid", isNoRfid);
	values.put("copytime", System.currentTimeMillis());

	int k = db.update(DbTableName.WATERINFO, values, "_id=?", new String[] { waterid });
	if (k > 0) {
	    System.out.println("保存成功");
	    b = true;

	}
	return b;

    }

    /**
     * 删除所属图片路径
     * 
     * @param otherid
     * @return
     */
    public boolean delectImage(String otherid) {
	int id = db.delete(DbTableName.RESOURCES, "otherid=? and status=?", new String[] { otherid, "2" });
	if (id > 0) {
	    return true;
	}
	return false;

    }

    /**
     * 删除一张图片
     * 
     * @param id
     * @return
     */
    public boolean delectOne(String id) {
	int ids = db.delete(DbTableName.RESOURCES, "_id=? and status=?", new String[] { id, "2" });
	if (ids > 0) {
	    return true;
	}
	return false;
    }

    /**
     * 添加一张图片路径
     * 
     * @param otherid
     * @param path
     * @return
     */
    public int insertImage(String otherid, String path, String status) {
	ContentValues value = new ContentValues();
	value.put("otherid", otherid);
	value.put("path", path);
	value.put("type", "1");
	value.put("status", status);
	super.insert(value, DbTableName.RESOURCES);
	int i = 0;
	String sql = "select max(_id) as maxid from " + DbTableName.RESOURCES;
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    i = cursor.getInt(cursor.getColumnIndex("maxid"));
	}
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查询该表册某一个所添加的图片
     * 
     * @param otherid
     * @return
     */
    public List<ResourcesPoVo> queryResour(String otherid, String status) {
	List<ResourcesPoVo> list = new ArrayList<ResourcesPoVo>();
	String sql = "select * from " + DbTableName.RESOURCES + " where otherid='" + otherid + "' and type='1' " + "and status='" + status + "' order by _id desc";

	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    ResourcesPoVo resour = new ResourcesPoVo();
	    resour.setId(cursor.getInt(cursor.getColumnIndex("_id")));
	    resour.setPath(cursor.getString(cursor.getColumnIndex("path")));
	    resour.setOtherid(cursor.getInt(cursor.getColumnIndex("otherid")));
	    File f = new File(resour.getPath());
	    if (f.exists()) {
		list.add(resour);
	    }

	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }

    /**
     * 通过水表编号查询未上传水表信息
     * 
     * @param waternumber
     * @return
     */
    public WaterInfoVO queryOneNoUpload(String waternumber, String waterid, String type) {
	String sql = "select * from waterinfo where iscopystatus='已抄' and isupload='0' and ismultiplecopy='" + type + "'";
	if (waternumber != null) {
	    sql = sql + " and waternumber='" + waternumber + "'";
	}
	if (waterid != null) {
	    sql = sql + " and _id=" + waterid;
	}
	Cursor cursor = db.rawQuery(sql, null);
	WaterInfoVO water = null;
	if (cursor.moveToNext()) {
	    water = new WaterInfoVO();
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    water.setSid(cursor.getString(cursor.getColumnIndex("sid")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    String lastwater = cursor.getString(cursor.getColumnIndex("lastmonthwater"));
	    water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    if (!TextUtils.isEmpty(lastwater)) {
		water.setLastmonthwater(Double.parseDouble(lastwater));
	    } else {
		water.setLastmonthwater(0.0);
	    }
	    water.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    water.setIsNorfid(cursor.getString(cursor.getColumnIndex("isnorfid")));
	}

	if (water != null) {
	    String sqlimgname = "select * from  " + DbTableName.RESOURCES + " where otherid='" + water.getId() + "' and type='1'";

	    Cursor c = db.rawQuery(sqlimgname, null);
	    List<String> imgpath = new ArrayList<String>();
	    while (c.moveToNext()) {

		imgpath.add(c.getString(c.getColumnIndex("path")));
	    }
	    if (c != null) {
		c.close();
	    }
	    if (cursor != null) {
		cursor.close();
	    }
	    water.setImagepath(imgpath);
	}
	return water;

    }

    /**
     * 查询所有未上传信息
     * 
     * @return
     */
    public List<WaterInfoVO> queryAllNoUpload() {
	Cursor cursor = null;
	Cursor cursorimg = null;
	WaterInfoVO water = null;
	List<WaterInfoVO> list = new ArrayList<WaterInfoVO>();
	String sql = "select * from waterinfo where iscopystatus='已抄' and isupload='0' and ismultiplecopy='0'";
	cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {

	    water = new WaterInfoVO();
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    water.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    water.setIsNorfid(cursor.getString(cursor.getColumnIndex("isnorfid")));
	    water.setAdds(cursor.getString(cursor.getColumnIndex("adds")));
	    System.out.println(water.getIsNorfid() + "是否读卡抄表");
	    String sql2 = "select * from " + DbTableName.RESOURCES + " where otherid='" + water.getId() + "' and type='1' and  status='2'";
	    cursorimg = db.rawQuery(sql2, null);

	    List<String> imgpath = new ArrayList<String>();

	    while (cursorimg.moveToNext()) {
		System.out.println(cursorimg.getColumnIndex("path"));
		imgpath.add(cursorimg.getString(cursorimg.getColumnIndex("path")));
	    }
	    water.setImagepath(imgpath);
	    list.add(water);
	    if (cursorimg != null) {
		cursorimg.close();
	    }
	}
	if (cursor != null) {
	    cursor.close();
	}

	return list;

    };

    /**
     * 通过水表编号查询水表信息
     * 
     * @param number
     * @return
     */
    public WaterInfoVO queryWaterinfo(String number) {
	if (number == null | number.equals("")) {
	    return null;
	}
	WaterInfoVO water = new WaterInfoVO();
	String sql = "select * from waterinfo where ismultiplecopy='0' and  waternumber='" + number + "'";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));

	}
	return water;

    }

    /**
     * 查询所有抄表数
     * 
     * @return
     */
    public int UploadWater() {
	int i = 0;
	String sql = "select _id from waterinfo where  iscopystatus='已抄' and ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    i = cursor.getCount();
	}
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查询所有未抄表数
     * 
     * @return
     */
    public int NoUploadWater() {
	int i = 0;
	String sql = "select _id from waterinfo where iscopystatus='已抄'and isupload='0' and ismultiplecopy='0'";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    i = cursor.getCount();
	}
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 更改状态
     * 
     * @param id
     * @return
     */
    public boolean uploadstatus(String id) {
	boolean b = false;
	ContentValues value = new ContentValues();
	value.put("isupload", "1");
	int i = db.update(DbTableName.WATERINFO, value, "_id=?", new String[] { id });
	if (i > 0) {
	    b = true;
	}
	return b;
    }

    /**
     * 查询所有未来上传水表数
     * 
     * @return
     */
    public int queryNoupload() {
	String sql = "select _id from waterinfo where isupload='0' and iscopystatus='已抄' and ismultiplecopy='0'";

	int i = 0;
	Cursor cursor = db.rawQuery(sql, null);

	if (cursor.moveToNext()) {
	    i = cursor.getCount();
	}
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查询所有抄表数
     * 
     * @return
     */
    public int queryCopyWater() {
	String sql = "select _id from waterinfo where  iscopystatus='已抄' and ismultiplecopy='0'";

	int i = 0;
	Cursor cursor = db.rawQuery(sql, null);

	if (cursor.moveToNext()) {
	    i = cursor.getCount();
	}
	if (cursor != null) {
	    cursor.close();
	}
	return i;
    }

    /**
     * 查询抄表是否拍照
     * 
     * @param waterid
     * @return
     */
    public boolean isPhotograph(String waterid) {
	boolean b = false;
	if (waterid == null || waterid.equals("")) {
	    return false;
	}
	String sql = "select * from " + DbTableName.RESOURCES + "  where status='2' and type='1' and otherid='" + waterid + "'";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {

	    b = true;
	}
	if (cursor != null) {
	    cursor.close();
	}
	return b;

    }

    /**
     * 查询下一组去抄的第一个水表信息
     * 
     * @param groupsort
     * @return
     */
    public WaterInfoVO queryNextWater(int groupsort) {
	WaterInfoVO water = null;
	String sql = "select * from waterinfo where  ismultiplecopy='0' and iscopystatus='未抄' ";
	if (groupsort > 0) {
	    sql = sql + " and groupingsort= '" + groupsort + "'";
	}
	sql = sql + " order by  waterlistid";
	System.out.println(sql);
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    water = new WaterInfoVO();
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    water.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    water.setIsNorfid(cursor.getString(cursor.getColumnIndex("isnorfid")));
	    water.setAdds(cursor.getString(cursor.getColumnIndex("adds")));
	    water.setGroupingname(cursor.getString(cursor.getColumnIndex("groupingname")));
	    water.setWaterlistid(cursor.getString(cursor.getColumnIndex("waterlistid")));
	    water.setGroupingsort(cursor.getInt(cursor.getColumnIndex("groupingsort")));
	    String groupwatersort = cursor.getString(cursor.getColumnIndex("groupwatersort"));
	    water.setGroupwatersort(Integer.parseInt(groupwatersort));

	}
	if (cursor != null) {
	    cursor.close();
	}
	return water;
    }

    /**
     * 查询最后一次抄表的信息
     * 
     * @return
     */
    public WaterInfoVO queryLastCopyWater() {
	WaterInfoVO water = null;
	// 查询最后一次抄表记录
	String sql = "select * from waterinfo where iscopystatus='未抄' and  ismultiplecopy='0' and copytime is not null order by copytime desc";
	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    water = new WaterInfoVO();
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    water.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    water.setIsNorfid(cursor.getString(cursor.getColumnIndex("isnorfid")));
	    water.setAdds(cursor.getString(cursor.getColumnIndex("adds")));
	    water.setGroupingname(cursor.getString(cursor.getColumnIndex("groupingname")));
	    water.setGroupingsort(cursor.getInt(cursor.getColumnIndex("groupingsort")));
	    water.setWaterlistid(cursor.getString(cursor.getColumnIndex("waterlistid")));
	    String groupwatersort = cursor.getString(cursor.getColumnIndex("groupwatersort"));
	    water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	    water.setGroupwatersort(Integer.parseInt(groupwatersort));
	}
	if (cursor != null) {
	    cursor.close();
	}

	// 如果没有最后一次抄表记录，则查询第一个水表信息
	if (water == null) {
	    String sql1 = "select * from waterinfo where iscopystatus='未抄' and ismultiplecopy='0' order by waterlistid,groupingsort, groupwatersort";
	    cursor = db.rawQuery(sql1, null);
	    if (cursor.moveToNext()) {
		water = new WaterInfoVO();
		water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
		water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
		water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
		water.setId(cursor.getString(cursor.getColumnIndex("_id")));
		water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
		water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
		water.setIsNorfid(cursor.getString(cursor.getColumnIndex("isnorfid")));
		water.setAdds(cursor.getString(cursor.getColumnIndex("adds")));
		water.setGroupingname(cursor.getString(cursor.getColumnIndex("groupingname")));
		water.setGroupingsort(cursor.getInt(cursor.getColumnIndex("groupingsort")));
		water.setWaterlistid(cursor.getString(cursor.getColumnIndex("waterlistid")));
		String groupwatersort = cursor.getString(cursor.getColumnIndex("groupwatersort"));
		water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
		water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
		water.setGroupwatersort(Integer.parseInt(groupwatersort));
	    }
	    if (cursor != null) {
		cursor.close();
	    }
	}
	return water;
    }

    /**
     * 查询提示信息
     * 
     * @param water
     * @return
     */
    public NoticeVo queryNotice(WaterInfoVO water) {
	NoticeVo notice = new NoticeVo();
	Cursor cursor = null;
	// 查询除开本表册外的表册
	String sql7 = "select * from waterlist where number not in (" + water.getWaterlistid() + ")";
	List<WaterlistPoVo> list = new ArrayList<WaterlistPoVo>();
	cursor = db.rawQuery(sql7, null);
	int k = cursor.getCount();
	while (cursor.moveToNext()) {
	    WaterlistPoVo waterlist = new WaterlistPoVo();
	    waterlist.setNumber(cursor.getString(cursor.getColumnIndex("number")));
	    waterlist.setName(cursor.getString(cursor.getColumnIndex("name")));
	    waterlist.setId(cursor.getInt(cursor.getColumnIndex("_id")));
	    list.add(waterlist);
	}
	if (cursor != null) {
	    cursor.close();
	}
	if (k > 0) {
	    for (int i = 0; i < list.size(); i++) {
		// 查询每个表册数
		String waterlistCount = "select * from waterinfo where waterlistid='" + list.get(i).getNumber() + "'";
		// 查询本表册未抄数
		String waterlistIsCopy = "select * from waterinfo where waterlistid='" + list.get(i).getNumber() + "' and " + " iscopystatus='未抄' ";

		cursor = db.rawQuery(waterlistCount, null);
		int number = cursor.getCount();
		list.get(i).setWaterCount(cursor.getCount());
		cursor = db.rawQuery(waterlistIsCopy, null);
		if (cursor.getCount() != 0 && cursor.getCount() != number) {
		    notice.setListwaterlistName(list.get(i).getName());
		    notice.setLastWaterlistNoCopy(cursor.getCount());
		    if (cursor != null) {
			cursor.close();
		    }
		    break;
		}

	    }

	}
	if (notice.getLastWaterlistNoCopy() == 0) {

	    if (water.getGroupingsort() == 1) {
		notice.setLastGroup(0);
		notice.setLastNoCopy(0);
	    } else {
		// 查询本表册上一组未抄的第一个水表信息
		String sql2 = "select * from waterinfo where ismultiplecopy='0'  and iscopystatus='未抄'" + "and waterlistid='" + water.getWaterlistid()
			+ "'  order by groupingsort, groupwatersort";
		cursor = db.rawQuery(sql2, null);
		if (cursor.moveToNext()) {
		    notice.setLastWaterAdds(cursor.getString(cursor.getColumnIndex("wateradds")));
		    notice.setLastgroupName(cursor.getString(cursor.getColumnIndex("groupingname")));
		    notice.setLastWaterid(cursor.getString(cursor.getColumnIndex("_id")));

		}
		if (cursor != null) {
		    cursor.close();
		}

		// 查询本表册上一组水表数
		String sql1 = "select * from waterinfo where ismultiplecopy='0'   and waterlistid='" + water.getWaterlistid() + "' and groupingname='" + notice.getLastgroupName()
			+ "'";
		cursor = db.rawQuery(sql1, null);
		if (cursor.moveToNext()) {
		    notice.setLastGroup(cursor.getCount());
		}
		if (cursor != null) {
		    cursor.close();
		}

		// 查询本表册上一组水表未抄数
		String sql6 = "select * from waterinfo where ismultiplecopy='0'   and waterlistid='" + water.getWaterlistid() + "' and groupingname='" + notice.getLastgroupName()
			+ "' and iscopystatus='未抄'";
		cursor = db.rawQuery(sql6, null);
		if (cursor.moveToNext()) {
		    notice.setLastNoCopy(cursor.getCount());
		}
		if (cursor != null) {
		    cursor.close();
		}
	    }
	}
	// 查询本表册本组数
	String sql3 = "select * from waterinfo where ismultiplecopy='0' and  groupingsort='" + water.getGroupingsort() + "'  and waterlistid='" + water.getWaterlistid() + "'";
	cursor = db.rawQuery(sql3, null);
	if (cursor.moveToNext()) {
	    notice.setThisGroup(cursor.getCount());
	}
	if (cursor != null) {
	    cursor.close();
	}

	// 查询本表册本组未抄水表数及第一个水表信息
	String sql4 = "select * from waterinfo where  groupingsort='" + water.getGroupingsort() + "'  and iscopystatus='未抄' " + " and waterlistid='" + water.getWaterlistid()
		+ "'  and  ismultiplecopy='0'  order by groupwatersort";
	System.out.println(sql4);
	cursor = db.rawQuery(sql4, null);
	if (cursor.moveToNext()) {
	    notice.setThisNoCopy(cursor.getCount());
	    if (notice.getThisGroup() > 0) {
		notice.setNextwateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
		notice.setThisgroupName(cursor.getString(cursor.getColumnIndex("groupingname")));
		notice.setNextWaterid(cursor.getString(cursor.getColumnIndex("_id")));
	    }

	}
	if (cursor != null) {
	    cursor.close();
	}
	return notice;

    }

    /**
     * 查询下一个应该抄的水表
     * 
     * @param water
     * @return
     */
    public WaterInfoVO queryNextWaterInfo(WaterInfoVO water) {

	WaterInfoVO w = null;

	Cursor cursor = null;
	// 查询本表册的下一个未抄表信息
	String sql = "select * from waterinfo where  waterlistid='" + water.getWaterlistid() + "'"
		+ "  and iscopystatus='未抄' and  ismultiplecopy='0'  order by groupingsort,groupwatersort";
	cursor = db.rawQuery(sql, null);

	int k = 0;
	while (cursor.moveToNext()) {
	    String waterid = cursor.getString(cursor.getColumnIndex("_id"));
	    if (k == 1) {
		w = new WaterInfoVO();
		w.setId(waterid);
		w.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
		w.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
		w.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
		w.setAdds(cursor.getString(cursor.getColumnIndex("adds")));

	    }
	    if (waterid.equals(water.getId())) {
		k = 1;
	    }
	}
	if (cursor != null) {
	    cursor.close();
	}
	if (k == 1) {
	    return w;
	}

	// 查询其它表册未抄的第一个水表信息
	String sql1 = "select * from waterinfo where waterlistid  not in (" + water.getWaterlistid() + ")  and iscopystatus='未抄' and ismultiplecopy='0'"
		+ "order by waterlistid,groupingsort,groupwatersort";
	cursor = db.rawQuery(sql1, null);
	if (cursor.moveToNext()) {
	    w = new WaterInfoVO();
	    w.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    w.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    w.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    w.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	    w.setAdds(cursor.getString(cursor.getColumnIndex("adds")));

	}
	if (cursor != null) {
	    cursor.close();
	}
	return w;

    }

    /**
     * 查询所有表册
     * 
     * @return
     */
    public List<WaterlistPoVo> queryAllWaterList() {
	List<WaterlistPoVo> list = new ArrayList<WaterlistPoVo>();
	String sql = "select * from waterlist";
	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    WaterlistPoVo water = new WaterlistPoVo();
	    water.setId(cursor.getInt(cursor.getColumnIndex("_id")));
	    water.setName(cursor.getString(cursor.getColumnIndex("name")));
	    water.setNumber(cursor.getString(cursor.getColumnIndex("number")));
	    list.add(water);
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }
}
